﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Lib.Controls;
using System.IO;
using System.Data.OleDb;
using System.Data;

public partial class importDiem : fpage
{
    OleDbConnection oledbConn;
    protected void Page_Load(object sender, EventArgs e)
    {
        ScriptManager.GetCurrent(this).Services.Add(new ServiceReference("~/Service/wht_nsd.svc"));
    }
    protected void AsyncFileUpload_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
    {
        if (AsyncFileUpload.HasFile)
        {
            string b_tenf = AsyncFileUpload.FileName.ToString();
            string tm = DateTime.Now.Year.ToString();
            string dd = Server.MapPath("~/Outputs/");
            dd = dd + tm;
            if (!Directory.Exists(dd))
                Directory.CreateDirectory(dd);
            //Upda 
            dd = dd + "/" + b_tenf;
            if (File.Exists(dd)) File.Delete(dd);
            AsyncFileUpload.PostedFile.SaveAs(dd);

            //string strPath = MapPath("~/VB/") + Path.GetFileName(e.filename);
            //AsyncFileUpload.SaveAs(strPath);

            DataTable dt_rs = GenerateExcelData(dd);

            Session["diem"] = dt_rs;
        }
    }

    private DataTable GenerateExcelData(string path)
    {
        try
        {
            if (Path.GetExtension(path) == ".xls")
            {
                oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            }
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Sheet1$]";
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            oledbConn.Close();
        }
    }
}